/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package Model;

import java.io.File;
import java.io.IOException;
import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.logging.Level;
import java.util.logging.Logger;
import jxl.Workbook;
import jxl.WorkbookSettings;
import jxl.read.biff.BiffException;

/**
 *
 * @author HaTschE
 */
public class LoadData {

    private Workbook workbook;

    public LoadData(File file) {
        try {
            WorkbookSettings ws = new WorkbookSettings();
            ws.setEncoding("Cp1252");
            workbook = Workbook.getWorkbook(new File(file.getAbsolutePath()), ws);

            this.loadFromXls();
            this.readInflation();
            this.loadGDPData();
//            this.includeInflation();
            this.loadClassData();
            workbook.close();
        } catch (IOException | BiffException ex) {
            Logger.getLogger(LoadData.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    public void readInflation() {
        for (int i = 0; i < Controller.Main.CONTROL.getCountries().size(); i++) {
            int rows = -1;
            int columns = -1;
            Country currentCountry = Controller.Main.CONTROL.getCountries().get(i);
            for (int row = 1; row < workbook.getSheet(1).getColumn(0).length; row++) {
                if (workbook.getSheet(1).getCell(1, row).getContents().contains(currentCountry.getName())) {
                    rows = row;
                    break;
                }
            }
            String test = currentCountry.getIndicators().get(0).getYears().get(0).getName();
            test = "" + test.subSequence(0, test.length() - 2);
            for (int column = 0; column < workbook.getSheet(1).getRow(0).length; column++) {
                if (workbook.getSheet(1).getCell(column, 0).getContents().contains(test)) {
                    columns = column;
                    break;
                }
            }
            ArrayList<String> inflation = new ArrayList<>();
            for (int column = columns; column < workbook.getSheet(1).getRow(0).length; column++) {
                String values = workbook.getSheet(1).getCell(column, rows).getContents();
                if (!values.isEmpty()) {
                    values = values.replace(",", ".");
                    inflation.add("" + Double.parseDouble(values));
                }
            }
            currentCountry.setInflation(inflation);
        }
    }

//    private void includeInflation() {
//        for (int i = 0; i < Controller.Main.CONTROL.getCountries().size(); i++) {
//            Country currentCountry = Controller.Main.CONTROL.getCountries().get(i);
//            for (int j = 0; j < currentCountry.getIndicators().size(); j++) {
//                for (int l = 0; l < currentCountry.getIndicators().get(j).getYears().size(); l++) {
//                    for (int m = 0; m < 4; m++) {
//                        String stringValue = currentCountry.getIndicators().get(j).getYears().get(l).getQuarterlyData()[m];
//                        if (!stringValue.contains("?")) {
//                            double value = Double.parseDouble(stringValue);
//                            for (int n = l; n < currentCountry.getInflation().size(); n++) {
//                                value *= Double.parseDouble(currentCountry.getInflation().get(n));
//                            }
//                            currentCountry.getIndicators().get(j).getYears().get(l).getQuarterlyData()[m] = "" + value;
//                        }
//                    }
//                }
//            }
//            for (int j = 0; j < currentCountry.getIndicators().size(); j++) {
//                for (int k = 0; k < currentCountry.getIndicators().get(j).getSubIndicators().size(); k++) {
//                    for (int l = 0; l < currentCountry.getIndicators().get(j).getSubIndicators().get(k).getYears().size(); l++) {
//                        for (int m = 0; m < 4; m++) {
//                            String stringValue = currentCountry.getIndicators().get(j).getSubIndicators().get(k).getYears().get(l).getQuarterlyData()[m];
//                            if (!stringValue.contains("?")) {
//                                double value = Double.parseDouble(stringValue);
//                                for (int n = l; n < currentCountry.getInflation().size(); n++) {
//                                    value *= Double.parseDouble(currentCountry.getInflation().get(n));
//                                }
//                                currentCountry.getIndicators().get(j).getSubIndicators().get(k).getYears().get(l).getQuarterlyData()[m] = "" + value;
//                            }
//                        }
//                    }
//                }
//            }
//        }
//    }
    public void loadClassData() {
        Sort qs = new Sort();
        for (int i = 0; i < Controller.Main.CONTROL.getCountries().size(); i++) {
            int rows = -1;
            int columns = -1;
            Country currentCountry = Controller.Main.CONTROL.getCountries().get(i);
            for (int row = 1; row < workbook.getSheet(3).getColumn(0).length; row++) {
                if (workbook.getSheet(3).getCell(1, row).getContents().contains(currentCountry.getName())) {
                    rows = row;
                    break;
                }
            }
            String test = currentCountry.getIndicators().get(0).getYears().get(0).getName();
            test = "" + test.subSequence(0, test.length() - 2);
            for (int column = 0; column < workbook.getSheet(3).getRow(0).length; column++) {
                if (workbook.getSheet(3).getCell(column, 0).getContents().contains(test)) {
                    columns = column;
                    break;
                }
            }
            for (int column = columns; column < workbook.getSheet(3).getRow(0).length; column++) {
                String values = workbook.getSheet(3).getCell(column, rows).getContents();
                if (!values.isEmpty()) {
                    values = values.replace(",", ".");
                    int index = 0;
                    double rate = Double.parseDouble(values);
                    if (!Controller.Main.CONTROL.getInterestrates().isEmpty()) {
                        index = qs.sort(Controller.Main.CONTROL.getInterestrateValues(), rate);
                    }
                    Controller.Main.CONTROL.getInterestrates().add(index, new Interestrates(i, workbook.getSheet(3).getCell(column, 0).getContents(), rate));
                    Controller.Main.CONTROL.getInterestrateValues().add(index, rate);
                }
            }
        }
    }

    public void loadGDPData() {
        for (int i = 0; i < Controller.Main.CONTROL.getCountries().size(); i++) {
            int rows = -1;
            int columns = -1;
            Country currentCountry = Controller.Main.CONTROL.getCountries().get(i);
            for (int row = 1; row < workbook.getSheet(2).getColumn(0).length; row++) {
                if (workbook.getSheet(2).getCell(1, row).getContents().contains(currentCountry.getName())) {
                    rows = row;
                    break;
                }
            }
            String test = currentCountry.getIndicators().get(0).getYears().get(0).getName();
            for (int column = 0; column < workbook.getSheet(2).getRow(0).length; column++) {
                if (workbook.getSheet(2).getCell(column, 0).getContents().contains(test)) {
                    columns = column;
                    break;
                }
            }
            if (rows != -1) {
                for (int column = columns; column < workbook.getSheet(2).getRow(0).length; column++) {
                    String values = workbook.getSheet(2).getCell(column, rows).getContents();
                    if (!values.isEmpty()) {
                        values = values.replace(",",".");
                        double rate = Double.parseDouble(values);
                        for (int j = 0; j < currentCountry.getIndicators().size(); j++) {
                            String help = "" + (BigDecimal.valueOf(rate * 1000000000)).toEngineeringString();
                            if (!help.contains("E-")) {
                                currentCountry.getIndicators().get(j).getYears().get((column - columns) / 4).getQuarterlyGDP()[(column - columns) % 4] = help;
                            } else {
                                currentCountry.getIndicators().get(j).getYears().get((column - columns) / 4).getQuarterlyGDP()[(column - columns) % 4] = "0.00000000000000001";
                            }
                            for (int k = 0; k < currentCountry.getIndicators().get(j).getSubIndicators().size(); k++) {
                                help = "" + (BigDecimal.valueOf(rate * 1000000000)).toEngineeringString();
                                if (!help.contains("E-")) {
                                    currentCountry.getIndicators().get(j).getSubIndicators().get(k).getYears().get((column - columns) / 4).getQuarterlyGDP()[(column - columns) % 4] = help;
                                } else {
                                    currentCountry.getIndicators().get(j).getSubIndicators().get(k).getYears().get((column - columns) / 4).getQuarterlyGDP()[(column - columns) % 4] = "0.00000000000000001";
                                }

                            }
                        }
                    }
                }
            }
        }
    }

    private void loadFromXls() {
//        load first country manually
        Country currentCountry = new Country(workbook.getSheet(0).getCell(1, 1).getContents());
        Controller.Main.CONTROL.addCountry(currentCountry);
        for (int row = 1; row < workbook.getSheet(0).getColumn(0).length; row++) {
            if (workbook.getSheet(0).getCell(1, row).getContents().matches(currentCountry.getName())) {
            } else {
//                new country detected
                currentCountry = new Country(workbook.getSheet(0).getCell(1, row).getContents());
                Controller.Main.CONTROL.addCountry(currentCountry);
            }
//  Loading of all indicator data for this country
            Indicator currentIndicator = new Indicator(workbook.getSheet(0).getCell(2, row).getContents().split("_")[2]);
            Year currentYear = new Year(workbook.getSheet(0).getCell(4, 0).getContents());

//                Skip to the value columns
            for (int column = 4; column < workbook.getSheet(0).getRow(0).length; column++) {

                String orgValue = workbook.getSheet(0).getCell(column, row).getContents();
                orgValue = orgValue.split(",")[0];
//  New Year 
                if ((column % 4) == 0) {
//First Year check
                    if (column != 4) {
                        currentIndicator.getYears().add(currentYear);
                    }
//                    Load the name of the Year
                    currentYear = new Year(workbook.getSheet(0).getCell(column, 0).getContents());
                    if (!orgValue.isEmpty()) {
                        currentYear.setQuarterlyOrg(0, orgValue);
                    } else {
                        currentYear.setQuarterlyOrg(0, "?");
                    }
                } else {
                    if (!orgValue.isEmpty()) {
                        currentYear.setQuarterlyOrg(column % 4, orgValue);
                    } else {
                        currentYear.setQuarterlyOrg(column % 4, "?");
                    }
                }

            }
            currentIndicator.getYears().add(currentYear);
            if (workbook.getSheet(0).getCell(2, row).getContents().contains("..")) {
                currentCountry.getIndicators().get(currentCountry.getIndicators().size() - 1).addSubIndicator(currentIndicator);
            } else {
                currentCountry.getIndicators().add(currentIndicator);
            }
        }
    }
}
